package gbench.sandbox.jdbc;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import org.junit.jupiter.api.Test;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import gbench.common.tree.LittleTree.Jdbc.JdbcQuery;
import gbench.common.tree.LittleTree.Jdbc.SqlPatternPreprocessor;
import gbench.commonApp.data.DataApp;
import gbench.commonApp.jdbc.IJdbcApp;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.Jdbc;
import gbench.common.tree.LittleTree.Jdbc.JdbcConfig;
import static gbench.common.tree.LittleTree.IRecord.*;
import static gbench.common.tree.LittleTree.*;

/**
 * Jdbc 额使用演示。
 * @author gbench
 *
 */
public class JunitJdbcApp {
    
    /**
     * 使用注解进行数据库连接。
     * @author gbench
     *
     */
    @JdbcConfig(url="jdbc:mysql://localhost:3306/information_schema?serverTimezone=GMT%2B8")
    interface MyDatabase {
        /**
         * 获取代理结构对象 一个类似于下面的的结构
         * 可以作为访问:sqlinterceptor，sqlpattern_preprocessor，jdbc_postprocessorjdbc等对象的一个入口。    
         * params: ""
         * method:public abstract gbench.common.tree.LittleTree$IRecord 
         *  gbench.demo.data.DataApp$SimpleDatabase.getProxy() 
         * sqlinterceptor:(null) sqlpattern_preprocessor:(null)  
         * jdbc_postprocessor:(null)   
         * jdbc:gbench.common.tree.LittleTree$Jdbc@26f67b76    
         * 
         * @return sqlpattern_preprocessor,interceptor,posprocessor 等内部结构。
         */
        public IRecord getProxy();
        
        /**
         * 所有的数据表信息
         * @return IRecord
         */
        @JdbcQuery
        List<IRecord> geAllTables();
        
        @JdbcQuery
        List<IRecord> getTablesOf(String schema );
        
        
        /**
         * 在注解中 使用 sql语句模版 :sqlpattern
         * @param schema 数据库名
         * @return 表数量
         */
        @JdbcQuery("select count(*) cnt from TABLES where TABLE_SCHEMA = ''{0}''")
        Long getTablesCount(String schema );
        
        /**
         * 使用nspp提取sql 文件中参数
         * @param schema 数据库名
         * @return 表数量
         */
        @JdbcQuery
        Long getTablesCount2(String schema );
        
        /**
         * 使用数据库注入jdbc
         * @param jdbc
         */
        public default Integer defaultQuery(String schema,Jdbc jdbc) {
            final var ai = new AtomicInteger();
            jdbc.withTransaction(sess->{
                for(int i=0;i<100;i++) {
                    final var t = sess.sql2get(MFT("select count(*) cnt from TABLES where TABLE_SCHEMA = ''{0}''",schema),Integer.class);
                    ai.set(t);
                }
            });
            return ai.get();
        };
        
        /**
         * 最后一个返回值类型的参数，如果传入null,并且类型与 方法的返回值相同,会示做返回值回调:会被自动注入 到default 接口之中。并通过 default 接口中的数据处理结果 后给予返回。
         * 在注解中 使用 sql语句模版 :sqlpattern
         * @param schema 数据库名
         * @param offset 结果偏移。
         * @return 表数量
         */
        @JdbcQuery("select count(*) cnt from TABLES where TABLE_SCHEMA = ''{0}''")
        default Long getTablesCount3(final String schema,final int offset , final Long result) {
            System.out.println("jdbc 为 result 注入了结果："+result);
            return offset+result;
        }
        
    }
    
    @Test
    public void main() {
        final var database = (MyDatabase) IJdbcApp.newNsppDBInstance("myjdbc.sql",MyDatabase.class);
        final var proxy = database.getProxy();
        final var jdbc = proxy.findOne(Jdbc.class);
        final var spp = proxy.findOne(SqlPatternPreprocessor.class);
        jdbc.withTransaction(sess->{
            var recs = sess.sql2records_ex("#getAllTables");
            recs = sess.sql2records("#getTablesOf",REC("schema","mydb"));
            recs.forEach(e->{
                System.out.println(e);
            });
        },Jdbc.M(SqlPatternPreprocessor.class,spp));
    }
    
    /**
     * 数据短路
     */
    @Test
    public void shortcut() {
        final var database = (MyDatabase) IJdbcApp.newNsppDBInstance("myjdbc.sql",MyDatabase.class);
        final var proxy = database.getProxy();
        final var jdbc = proxy.findOne(Jdbc.class);
        jdbc.sql2recordS("select *  from TABLES").limit(3).forEach(e->{
            System.out.println(e);
        }); // 调用短路命令 会 保留数据库连接
        System.out.println("-------------------------------------------------------------");
        jdbc.clear(); // 清空数据流
        jdbc.dump();
    }
    
    /**
     * 数据短路
     */
    @Test
    public void shortcut2() {
        final var database = (MyDatabase) IJdbcApp.newNsppDBInstance("myjdbc.sql", MyDatabase.class);
        final var proxy = database.getProxy();
        final var jdbc = proxy.findOne(Jdbc.class);
        jdbc.withTransaction(sess -> {
            sess.sql2recordS("select *  from TABLES").limit(3).forEach(e -> {
                System.out.println(e);
            });
        });
        System.out.println("-------------------------------------------------------------");
        jdbc.dump(); // withTransaction 会自动清空 session
    }
    
    /**
     * 指定数据库连接。
     */
    @Test
    public void foo() {
        final var cfg = REC(
            "url", "jdbc:mysql://localhost:3306/information_schema?useSSL=false&serverTimezone=GMT%2B8&AllowPublicKeyRetrieval=True",
            "driver", "com.mysql.cj.jdbc.Driver",
            "user", "root",
            "password", "123456"
        );// cfg
        final var nspp = IJdbcApp.nspp("myjdbc.sql", MyDatabase.class);
        final var database = Jdbc.newInstance(MyDatabase.class,cfg,nspp );
        final var proxy = database.getProxy();
        final var jdbc = proxy.findOne(Jdbc.class);
        final var spp = proxy.findOne(SqlPatternPreprocessor.class);
        jdbc.withTransaction(sess->{
            var recs = sess.sql2records_ex("#getAllTables");
            recs = sess.sql2records("#getTablesOf",REC("schema","mydb"));
            recs.forEach(e->{
                System.out.println(e);
            });
            
            //sess.sql2get
            System.out.println("getTablesCount2:"+sess.sql2get("#getTablesCount2", REC("schema","mydb"), Long.class));
            System.out.println("getTablesCount:"+database.getTablesCount("mydb"));
            System.out.println("defaultQuery:"+database.defaultQuery("mydb",null));
            System.out.println("defaultQuery:"+database.getTablesCount3("mydb",10,null));
        },Jdbc.M(SqlPatternPreprocessor.class,spp));
        
    }
    
    
    
    /**
     * 自定义Jdbc
     * @author gbench
     */
    static class MyJdbc extends Jdbc{

        public MyJdbc(Map<?, ?> map) {
            super(map);
        }
        
        /**
         * 创建jdbc连接对象
         */
        public void init( final String driver, final String url, final String user, final String password){
            System.out.println("初始化数据库Jdbc:"+this.getClass());
            final var config = new HikariConfig();
            config.setJdbcUrl( url );
            config.setUsername( user);
            config.setPassword( password);
            config.addDataSourceProperty( "cachePrepStmts" , "true" );
            config.addDataSourceProperty( "prepStmtCacheSize" , "250" );
            config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );
            final var ds = new HikariDataSource( config );
            this.supplierConn= () -> {
                try {
                    if(this.ds==null)this.ds = ds;
                    return ds.getConnection();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return null;
            };
        }
        
        /**
         * 数据关闭。
         */
        public void close() {
            ds.close();
        }
        
        HikariDataSource ds = null;
        
    }
    
    /**
     * 直接传入类名路径
     */
    @Test
    public void bar() {
        var cfg = REC("url","jdbc:mysql://localhost:3306/information_schema?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=True",
            "driver","com.mysql.cj.jdbc.Driver","user","root","password","123456",
            "jdbcClass",MyJdbc.class.getName());
        final var database = Jdbc.newInstance(DataApp.SimpleDatabase.class,cfg);
        final var jdbc = database.getProxy().findOne(Jdbc.class);
        jdbc.sql2records("show tables").forEach(System.out::println);
    }
    
    /**
     * 直接传入class
     */
    @Test
    public void bar2() {
        var cfg = REC("url","jdbc:mysql://localhost:3306/information_schema?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=True",
            "driver","com.mysql.cj.jdbc.Driver","user","root","password","123456",
            "jdbcClass",MyJdbc.class);
        final var database = Jdbc.newInstance(DataApp.SimpleDatabase.class,cfg);
        final var jdbc = database.getProxy().findOne(Jdbc.class);
        jdbc.sql2records("show tables").forEach(System.out::println);
    }
    
    /**
     * 直接传入类实例
     */
    @Test
    public void qux() {
        var cfg = REC("url","jdbc:mysql://localhost:3306/information_schema?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=True",
            "driver","com.mysql.cj.jdbc.Driver","user","root","password","123456");
        class MyJdbc2 extends MyJdbc{// 内置一个数据库
            public MyJdbc2(Map<?, ?> map) {
                super(map);
            }};
        final var database = Jdbc.newInstance(DataApp.SimpleDatabase.class,REC("_",new MyJdbc2(cfg.toMap())));
        final var jdbc = database.getProxy().findOne(Jdbc.class);
        jdbc.sql2records("show tables").forEach(System.out::println);
    }

}
